package com.source.lightfowl.labelingRecord.dao;


import com.source.lightfowl.labelingRecord.pojo.Record;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 数据访问接口
 *
 * @author Administrator
 */
public interface RecordDao extends JpaRepository<Record, BigInteger>, JpaSpecificationExecutor<Record> {
    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "WEEK(LabelingDate)," +
                    "poultryCategoryId," +
                    "COUNT(poultryCategoryId)" +
                    "FROM labeling_record" +
                    "WHERE poultryCategoryId=(?1) " +
                    "GROUP BY poultryCategoryId" +
                    "ORDER BY WEEK(LabelingDate)"
    )
    List<Record> findRecodeByPoultryId(int id);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "WEEK(LabelingDate) AS `week`," +
                    "poultryCategoryId," +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "GROUP BY WEEK(LabelingDate),poultryCategoryId " +
                    "ORDER BY WEEK(LabelingDate) ASC"
    )
    List<Object> findGroupByWeekAndPoultry();

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "GROUP BY WEEK(LabelingDate),poultryCategoryId " +
                    "ORDER BY WEEK(LabelingDate) ASC"
    )
    BigInteger findCountByPoultryId(String id);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "AND YEAR(LabelingDate)=YEAR(NOW())"
    )
    BigInteger findCountByParentThisYear(List<String> ids);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) "
    )
    BigInteger findCountByParent(List<String> ids);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(LabelingDate)"
    )
    BigInteger findCountByParentInAMonth(List<String> ids);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "AND MONTH(LabelingDate)=(?2)"
    )
    BigInteger findCountByParentInMonth(List<String> ids, int month);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "AND DATE_SUB(CURDATE(), INTERVAL ?2 DAY) = DATE(LabelingDate)"
    )
    BigInteger findCountAndDateByParentInAMonth(List<String> ids, Integer numOfMonth);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "WEEK(LabelingDate) AS `week` " +
                    "FROM labeling_record " +
                    "GROUP BY WEEK(LabelingDate) " +
                    "ORDER BY WEEK(LabelingDate) ASC"
    )
    List<Integer> findAllWeekNum();

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "poultryCategoryId " +
                    "FROM labeling_record " +
                    "GROUP BY WEEK(LabelingDate),poultryCategoryId " +
                    "ORDER BY WEEK(LabelingDate) ASC"
    )
    List<String> findAllIdGroupByWeekAndPoultry();

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(poultryCategoryId) AS salesSum " +
                    "FROM labeling_record " +
                    "WHERE WEEK(LabelingDate)=?1 " +
                    "AND poultryCategoryId=?2 " +
                    "GROUP BY WEEK(LabelingDate),poultryCategoryId " +
                    "ORDER BY WEEK(LabelingDate) ASC"
    )
    Integer findNumOfSaleByWeekAndId(Integer week, String id);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(id),YEAR(labelingDate) AS `year` " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "GROUP BY `year` " +
                    "ORDER BY `year` ASC"
    )
    List<Object> findCountOfPoultryInYear(List<String> ids);

    @Query(
            nativeQuery = true,
            value = "SELECT SUM(`sale_count`) " +
                    "FROM " +
                    "(SELECT " +
                    "COUNT(id) AS `sale_count` " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "AND LabelingDate>=DATE_SUB(CURDATE(),INTERVAL 1 MONTH) " +
                    "AND LabelingDate<DATE_SUB(CURDATE(),INTERVAL 20 DAY) " +
                    "GROUP BY poultryCategoryId) AS `count_table`"
    )
    BigInteger findSalesThirtyDaysAgo1(List<String> ids);

    @Query(
            nativeQuery = true,
            value = "SELECT SUM(`sale_count`) " +
                    "FROM " +
                    "(SELECT " +
                    "COUNT(id) AS `sale_count` " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "AND LabelingDate>=DATE_SUB(CURDATE(),INTERVAL 20 DAY) " +
                    "AND LabelingDate<DATE_SUB(CURDATE(),INTERVAL 10 DAY) " +
                    "GROUP BY poultryCategoryId) AS `count_table`"
    )
    BigInteger findSalesThirtyDaysAgo2(List<String> ids);

    @Query(
            nativeQuery = true,
            value = "SELECT SUM(`sale_count`) " +
                    "FROM " +
                    "(SELECT " +
                    "COUNT(id) AS `sale_count` " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN(?1) " +
                    "AND LabelingDate>=DATE_SUB(CURDATE(),INTERVAL 10 DAY) " +
                    "AND LabelingDate<CURDATE() " +
                    "GROUP BY poultryCategoryId) AS `count_table`"
    )
    BigInteger findSalesThirtyDaysAgo3(List<String> ids);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "DATE_SUB(CURDATE(),INTERVAL 1 MONTH)," +
                    "DATE_SUB(CURDATE(),INTERVAL 21 DAY)," +
                    "DATE_SUB(CURDATE(),INTERVAL 20 DAY)," +
                    "DATE_SUB(CURDATE(),INTERVAL 11 DAY)," +
                    "DATE_SUB(CURDATE(),INTERVAL 10 DAY)," +
                    "DATE_SUB(CURDATE(),INTERVAL 1 DAY)"
    )
    List<Object> findTenDaysBorder();

    /*
    根据档口查询出进一个月的条码
     */
    @Query(
            nativeQuery = true,
            value = "SELECT * FROM labeling_record s WHERE s.stallsId IN (?1) AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(LabelingDate)"
    )
    List<Record> findByBarcodeMaChineIdNearThirtyDay(List<Integer> list);


    /*
    根据档口查询出某个时间段的条码
     */
    @Query(
            nativeQuery = true,
            value = "SELECT * FROM labeling_record s WHERE s.stallsId IN (?1) AND s.LabelingDate >=(?2) and s.LabelingDate <= (?3)"
    )
    List<Record> findByBarcodeMaChineIdNearThirtyDay(List<Integer> list, String startDate, String endDate);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(*) FROM labeling_record s WHERE poultryCategoryId IN(?3) AND s.LabelingDate >=(?1) and s.LabelingDate <= (?2)"
    )
    BigInteger findAllNearTime(String startDate, String endDate, List<String> poultryIds);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(*) FROM (SELECT * FROM labeling_record s WHERE s.stallsId IN (?1) AND s.LabelingDate >=(?2) and s.LabelingDate <= (?3)) AS `table` WHERE poultryCategoryId IN(?4)"
    )
    BigInteger findByStallNearTime(List<Integer> list, String startDate, String endDate, List<String> poultryIds);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(*) FROM (SELECT * FROM labeling_record s WHERE s.stallsId IN (?1) AND DATE(LabelingDate)=?3) AS `table` WHERE poultryCategoryId IN(?2)"
    )
    BigInteger findByStallNearTime(List<Integer> list, List<String> poultryIds, Date date);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(*) FROM labeling_record s WHERE poultryCategoryId IN(?1) AND DATE(LabelingDate)=?2"
    )
    BigInteger findByStallNearTime(List<String> poultryIds, Date date);
    /*
    根据档口id查询出档口近一个月的条码
     */
    @Query(
            nativeQuery = true,
            value = "SELECT * FROM labeling_record s WHERE s.stallsId IN (?1) AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(LabelingDate)"
    )
    long findCountByBarcodeMaChineId(Integer i);

    //SELECT a.`LabelingDate` FROM labeling_record a
    //    WHERE  a.`LabelingDate` >=  '2020-08-10'
    //    AND a.`LabelingDate` <= '2020-08-31'

    @Query(nativeQuery = true,
            value = "select toMarkId from labeling_record s where s.stallsId in (?1)")
    List<String> findByStallsId(List<String> stallsIdList);

    @Query(nativeQuery = true,
        value = "SELECT COUNT(id) FROM `labeling_record` WHERE stallsId = (?1) AND TO_DAYS(LabelingDate)=TO_DAYS(NOW());")
    BigInteger findCountByStallsThisDay(Integer stallsId);

    @Query(nativeQuery = true,
            value = "SELECT COUNT(id) FROM `labeling_record` WHERE stallsId = (?1) AND YEARWEEK(DATE_FORMAT(LabelingDate,'%Y-%m-%d'))=YEARWEEK(NOW());")
    BigInteger findCountByStallsThisWeek(Integer stallsId);

    @Query(nativeQuery = true,
            value = "SELECT COUNT(id) FROM `labeling_record` WHERE stallsId = (?1) AND DATE_FORMAT(LabelingDate,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');")
    BigInteger findCountByStallsThisMonth(Integer stallsId);

    @Query(nativeQuery = true,
            value = "SELECT a.poultryParentName, IFNULL(num, 0) num\n" +
                    "FROM\n" +
                    "( SELECT id, poultryParentName FROM `poultry_category_parent`\n" +
                    ") a LEFT JOIN\n" +
                    "( SELECT pc.parentId poultryCategoryParentId, COUNT(lr.id) num\n" +
                    "FROM `labeling_record` lr, `poultry_category` pc \n" +
                    "WHERE stallsId = (?1) \n" +
                    "AND TO_DAYS(LabelingDate)=TO_DAYS(NOW())\n" +
                    "AND lr.`poultryCategoryId` = pc.`id`\n" +
                    "GROUP BY poultryCategoryParentId\n" +
                    ") b\n" +
                    "ON a.id = b.poultryCategoryParentId;")
    List<Map<String,BigInteger>> findCountByStallsOrderByPoultryThisDay(Integer stallsId);

    @Query(nativeQuery = true,
            value = "SELECT a.poultryParentName, IFNULL(num, 0) num\n" +
                    "FROM\n" +
                    "( SELECT id, poultryParentName FROM `poultry_category_parent`\n" +
                    ") a LEFT JOIN\n" +
                    "( SELECT pc.parentId poultryCategoryParentId, COUNT(lr.id) num\n" +
                    "FROM `labeling_record` lr, `poultry_category` pc \n" +
                    "WHERE stallsId = (?1) \n" +
                    "AND YEARWEEK(DATE_FORMAT(LabelingDate,'%Y-%m-%d'))=YEARWEEK(NOW())\n" +
                    "AND lr.`poultryCategoryId` = pc.`id`\n" +
                    "GROUP BY poultryCategoryParentId\n" +
                    ") b\n" +
                    "ON a.id = b.poultryCategoryParentId;")
    List<Map<String,BigInteger>> findCountByStallsOrderByPoultryThisWeek(Integer stallsId);

    @Query(nativeQuery = true,
        value = "SELECT a.poultryParentName, IFNULL(num, 0) num\n" +
                "FROM\n" +
                "( SELECT id, poultryParentName FROM `poultry_category_parent`\n" +
                ") a LEFT JOIN\n" +
                "( SELECT pc.parentId poultryCategoryParentId, COUNT(lr.id) num\n" +
                "FROM `labeling_record` lr, `poultry_category` pc \n" +
                "WHERE stallsId = (?1) \n" +
                "AND DATE_FORMAT(LabelingDate,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')\n" +
                "AND lr.`poultryCategoryId` = pc.`id`\n" +
                "GROUP BY poultryCategoryParentId\n" +
                ") b\n" +
                "ON a.id = b.poultryCategoryParentId;")
    List<Map<String,BigInteger>> findCountByStallsOrderByPoultryThisMonth(Integer stallsId);

    @Query(nativeQuery = true,
            value = "SELECT a.poultryParentName, b.toMarkName, IFNULL(num, 0) num\n" +
                    "FROM\n" +
                    "( SELECT id poultryCategoryParentId, poultryParentName FROM `poultry_category_parent`\n" +
                    ") a LEFT JOIN\n" +
                    "( SELECT pc.parentId poultryCategoryParentId, tm.name ToMarkName, COUNT(lr.id) num\n" +
                    "FROM `labeling_record` lr, `poultry_category` pc, to_market tm\n" +
                    "WHERE stallsId = (?1) \n" +
                    "AND TO_DAYS(LabelingDate)=TO_DAYS(NOW())\n" +
                    "AND lr.`poultryCategoryId` = pc.`id`\n" +
                    "AND lr.`toMarkId` = tm.`id`\n" +
                    "GROUP BY tm.name, poultryCategoryParentId\n" +
                    ") b\n" +
                    "ON a.poultryCategoryParentId = b.poultryCategoryParentId\n" +
                    "ORDER BY num DESC;")
    List<Map<Object,Object>> findCountByStallsOrderByPoultryAndToMarkThisDay(Integer id);

    @Query(nativeQuery = true,
            value = "SELECT a.poultryParentName, b.toMarkName, IFNULL(num, 0) num\n" +
                    "FROM\n" +
                    "( SELECT id poultryCategoryParentId, poultryParentName FROM `poultry_category_parent`\n" +
                    ") a LEFT JOIN\n" +
                    "( SELECT pc.parentId poultryCategoryParentId, tm.name ToMarkName, COUNT(lr.id) num\n" +
                    "FROM `labeling_record` lr, `poultry_category` pc, to_market tm\n" +
                    "WHERE stallsId = (?1) \n" +
                    "AND YEARWEEK(DATE_FORMAT(LabelingDate,'%Y-%m-%d'))=YEARWEEK(NOW())\n" +
                    "AND lr.`poultryCategoryId` = pc.`id`\n" +
                    "AND lr.`toMarkId` = tm.`id`\n" +
                    "GROUP BY tm.name, poultryCategoryParentId\n" +
                    ") b\n" +
                    "ON a.poultryCategoryParentId = b.poultryCategoryParentId\n" +
                    "ORDER BY num DESC;")
    List<Map<Object,Object>> findCountByStallsOrderByPoultryAndToMarkThisWeek(Integer id);

    @Query(nativeQuery = true,
            value = "SELECT a.poultryParentName, b.toMarkName, IFNULL(num, 0) num\n" +
                    "FROM\n" +
                    "( SELECT id poultryCategoryParentId, poultryParentName FROM `poultry_category_parent`\n" +
                    ") a LEFT JOIN\n" +
                    "( SELECT pc.parentId poultryCategoryParentId, tm.name ToMarkName, COUNT(lr.id) num\n" +
                    "FROM `labeling_record` lr, `poultry_category` pc, to_market tm\n" +
                    "WHERE stallsId = (?1) \n" +
                    "AND DATE_FORMAT(LabelingDate,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')\n" +
                    "AND lr.`poultryCategoryId` = pc.`id`\n" +
                    "AND lr.`toMarkId` = tm.`id`\n" +
                    "GROUP BY tm.name, poultryCategoryParentId\n" +
                    ") b\n" +
                    "ON a.poultryCategoryParentId = b.poultryCategoryParentId\n" +
                    "ORDER BY num DESC;")
    List<Map<Object,Object>> findCountByStallsOrderByPoultryAndToMarkThisMonth(Integer id);


    //根据id查询全部信息
    @Query(nativeQuery = true,
    value = "select * from labeling_record where barcodeNumber like ?1 LIMIT 1")
    Record findAllByBarcode(String barcodeNumber);

    @Query(nativeQuery = true,
    value = "select * from labeling_record order by LabelingDate desc")
    List<Record> findAll();
}
